﻿Imports System
Imports System.Xml
Imports System.Threading
Imports System.Runtime.InteropServices
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Management.IntegrationServices
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common.SqlConnectionInfo
Imports System.Data.SqlClient
Imports System.Collections.ObjectModel


<DtsTask(DisplayName:="Execute Catalog Package Task", _
IconResource:="LPExecutePackageTask.LPExecutePackageTaskIcon.ico", _
Description:="A task that executes SSIS packages.", _
UITypeName:="LPExecutePackageTaskUI.LPExecutePackageTaskUI,LPExecutePackageTaskUI,Version=1.0.0.0,Culture=Neutral,PublicKeyToken=73de035d91ffde49")> _
Public NotInheritable Class LPExecutePackageTask
    Inherits Task
    Implements IDTSComponentPersist, IDTSBreakpointSite

#Region " Variables "

    Private _serverName As String
    Private _packageCatalog As String = "SSISDB"
    Private _packageFolder As String
    Private _packageProject As String
    Private _packageName As String

    Private postExecutionValue As Integer
    Private suspendRequiredValue As Integer
    Private debugModeValue As Integer
    Private bpm As BreakpointManager
    Private eventInfosValue As EventInfos
    Private variableDispenserValue As VariableDispenser

    Private suspended As ManualResetEvent
    Private canExecute As ManualResetEvent

    Private bFireError As Boolean = True
    Private bFireWarning As Boolean = True

#End Region

#Region " Constructor "

    Public Sub New()
        Me.suspended = New ManualResetEvent(True)
        Me.canExecute = New ManualResetEvent(True)
    End Sub

#End Region

#Region " Properties "

    Public Property ServerName As String
        Get
            Return _serverName
        End Get
        Set(value As String)
            _serverName = value
        End Set
    End Property

    Public Property PackageCatalog As String
        Get
            'Return _packageCatalog
            Return _packageCatalog
        End Get
        Set(value As String)
            _packageCatalog = value
        End Set
    End Property

    Public Property PackageFolder As String
        Get
            Return _packageFolder
        End Get
        Set(value As String)
            _packageFolder = value
        End Set
    End Property

    Public Property PackageProject As String
        Get
            Return _packageProject
        End Get
        Set(value As String)
            _packageProject = value
        End Set
    End Property

    Public Property PackageName As String
        Get
            Return _packageName
        End Get
        Set(value As String)
            _packageName = value
        End Set
    End Property

#End Region

#Region " Base Class Overrides "

    Public Overrides Sub InitializeTask(ByVal connections As Connections, _
                                        ByVal variableDispenser As VariableDispenser, _
                                        ByVal events As IDTSInfoEvents, _
                                        ByVal log As IDTSLogging, _
                                        ByVal eventInfos As EventInfos, _
                                        ByVal logEntryInfos As LogEntryInfos, _
                                        ByVal refTracker As ObjectReferenceTracker)
        If eventInfos Is Nothing Then
            Throw New ArgumentNullException("eventInfos")
        End If

        Me.eventInfosValue = eventInfos

        If variableDispenser Is Nothing Then
            Throw New ArgumentNullException("variableDispenser")
        End If

    End Sub

    Public Overrides Function Execute(ByVal connections As Connections, _
                                      ByVal variableDispenser As VariableDispenser, _
                                      ByVal componentEvents As IDTSComponentEvents, _
                                      ByVal log As IDTSLogging, _
                                      ByVal transaction As Object) As DTSExecResult
        If componentEvents Is Nothing Then
            Throw New ArgumentNullException("componentEvents")
        End If

        If variableDispenser Is Nothing Then
            Throw New ArgumentNullException("variableDispenser")
        End If

        ChangeEvent(suspended, False)

        CheckAndSuspend()

        If componentEvents.FireQueryCancel() = True Then
            Return DTSExecResult.Canceled
        End If

        Dim oServer As New Server(Me.ServerName)
        Dim oIS As New IntegrationServices(oServer)
        Dim cat As Catalog = oIS.Catalogs(Me.PackageCatalog)
        Dim fldr As CatalogFolder = cat.Folders(Me.PackageFolder)
        Dim prj As ProjectInfo = fldr.Projects(Me.PackageProject)
        Dim pkg As Microsoft.SqlServer.Management.IntegrationServices.PackageInfo = prj.Packages(Me.PackageName)


        pkg.Execute(False, Nothing, Nothing)

        ChangeEvent(suspended, True)

        Return DTSExecResult.Success

    End Function

    Public Overrides Function Validate(ByVal connections As Connections, _
                                       ByVal variableDispenser As VariableDispenser, _
                                       ByVal componentEvents As IDTSComponentEvents, _
                                       ByVal log As IDTSLogging) As DTSExecResult
        If componentEvents Is Nothing Then
            Throw New ArgumentNullException("componentEvents")
        End If

        If Me.ServerName = "" Then
            componentEvents.FireError(0, "ExecuteCatalogPackageTask", "ServerName cannot be empty.", "", 0)
        End If

        If Me.PackageCatalog = "" Then
            ' currently, there can be only noe catalog and it's name is SSISDB
            ' This is here for future versions
            'componentEvents.FireError(0, "ExecuteCatalogPackageTask", "PackageName cannot be empty.", "", 0)
            ' for now, set the default and raise an information message
            _packageCatalog = "SSISDB"
            componentEvents.FireInformation(1001, "", "PackageCatalog property defaulted to 'SSISDB'.", "", 0, True)
        End If

        If Me.PackageName = "" Then
            componentEvents.FireError(0, "ExecuteCatalogPackageTask", "PackageName cannot be empty.", "", 0)
        End If

        If Me.PackageProject = "" Then
            componentEvents.FireError(0, "ExecuteCatalogPackageTask", "PackageProject cannot be empty.", "", 0)
        End If

        If Me.PackageFolder = "" Then
            componentEvents.FireError(0, "ExecuteCatalogPackageTask", "PackageFolder cannot be empty.", "", 0)
        End If

        Return DTSExecResult.Success

    End Function

    Public Overrides ReadOnly Property ExecutionValue() As Object
        Get
            Return Me.postExecutionValue
        End Get
    End Property

#End Region

#Region " IDTSComponentPersist Members "

    Sub SaveToXML(ByVal doc As System.Xml.XmlDocument, ByVal infoEvents As IDTSInfoEvents) Implements IDTSComponentPersist.SaveToXML
        If doc Is Nothing Then
            Throw New ArgumentNullException("doc")
        End If

        Dim taskElement As XmlElement = doc.CreateElement(String.Empty, "LPExecutePackageTask", String.Empty)

        '	ServerName property
        Dim serverNameAttribute As XmlAttribute = doc.CreateAttribute(String.Empty, "ServerName", String.Empty)
        serverNameAttribute.Value = Me._serverName
        taskElement.Attributes.Append(serverNameAttribute)

        '	PackageCatalog property
        Dim packageCatalogAttribute As XmlAttribute = doc.CreateAttribute(String.Empty, "PackageCatalog", String.Empty)
        packageCatalogAttribute.Value = Me._packageCatalog
        taskElement.Attributes.Append(packageCatalogAttribute)

        '	PackageFolder property
        Dim packageFolderAttribute As XmlAttribute = doc.CreateAttribute(String.Empty, "PackageFolder", String.Empty)
        packageFolderAttribute.Value = Me._packageFolder
        taskElement.Attributes.Append(packageFolderAttribute)

        '	PackageProject property
        Dim packageProjectAttribute As XmlAttribute = doc.CreateAttribute(String.Empty, "PackageProject", String.Empty)
        packageProjectAttribute.Value = Me._packageProject
        taskElement.Attributes.Append(packageProjectAttribute)

        '	PackageName property
        Dim packageNameAttribute As XmlAttribute = doc.CreateAttribute(String.Empty, "PackageName", String.Empty)
        packageNameAttribute.Value = Me._packageName
        taskElement.Attributes.Append(packageNameAttribute)

        doc.AppendChild(taskElement)

    End Sub

    Sub LoadFromXML(ByVal node As System.Xml.XmlElement, ByVal infoEvents As IDTSInfoEvents) Implements IDTSComponentPersist.LoadFromXML
        If node Is Nothing Then
            Throw New ArgumentNullException("node")
        End If

        If node.Name <> "LPExecutePackageTask" Then
            Throw New Exception("Unexpected task element when loading task.")
        Else
            Try
                Me._serverName = CStr(node.Attributes.GetNamedItem("ServerName").Value)
                Me._packageCatalog = CStr(node.Attributes.GetNamedItem("PackageCatalog").Value)
                Me._packageFolder = CStr(node.Attributes.GetNamedItem("PackageFolder").Value)
                Me._packageProject = CStr(node.Attributes.GetNamedItem("PackageProject").Value)
                Me._packageName = CStr(node.Attributes.GetNamedItem("PackageName").Value)
            Catch
                Throw
            End Try
        End If
    End Sub

#End Region

#Region " IDTSBreakpointSite Members "

    Sub AcceptBreakpointManager(ByVal breakpointManager As BreakpointManager) Implements IDTSBreakpointSite.AcceptBreakpointManager
        If breakpointManager Is Nothing Then
            Throw New ArgumentNullException("breakpointManager")
        End If

        Me.bpm = breakpointManager
        'Me.bpm.CreateBreakpointTarget(1, "Break when the container receives the OnBeforeIncrement event.")
        'Me.bpm.CreateBreakpointTarget(2, "Break when the container receives the OnAfterIncrement event.")
    End Sub

    Property DebugMode() As Boolean Implements IDTSBreakpointSite.DebugMode
        Get
            Return Me.debugModeValue <> 0
        End Get
        Set(ByVal value As Boolean)
            Interlocked.Exchange((Me.debugModeValue), IIf(value, 1, 0)) 'TODO: For performance reasons this should be changed to nested IF statements
        End Set
    End Property
#End Region

#Region " IDTSSuspend Members "

    Property SuspendRequired() As Boolean Implements IDTSSuspend.SuspendRequired
        Get
            Return Me.suspendRequiredValue <> 0
        End Get
        Set(ByVal value As Boolean)
            ' This lock is also taken by Suspend().  Since it is possible for the package to be
            ' suspended and resumed in quick succession, this property put might happen before
            ' the actual Suspend() call.  Without the lock, the Suspend() might reset the canExecute
            ' event after we set it to abort the suspension.
            SyncLock Me
                Interlocked.Exchange((Me.suspendRequiredValue), IIf(value, 1, 0)) 'TODO: For performance reasons this should be changed to nested IF statements

                If Not value Then
                    ChangeEvent(canExecute, True)
                End If
            End SyncLock
        End Set

    End Property

    Sub ResumeExecution() Implements IDTSSuspend.ResumeExecution
        ChangeEvent(canExecute, True)
    End Sub

    Sub SuspendExecution() Implements IDTSSuspend.SuspendExecution
        ' This lock is also taken by the set SuspendRequired method.  It prevents this
        ' call from override an aborted suspension.  See comments in set SuspendRequired.
        SyncLock Me
            ' If a suspend is required, do it.
            If Me.suspendRequiredValue <> 0 Then
                ChangeEvent(canExecute, False)
            End If
        End SyncLock

        ' We can't return from Suspend until the task is "suspended".  This can happen
        ' one of two ways: the suspended event occurs, indicating that the execute thread
        ' has suspended or the canExecute flag is set, indicating that a suspend is no
        ' longer required.
        Dim suspendOperationComplete As WaitHandle() = {suspended, canExecute}
        WaitHandle.WaitAny(suspendOperationComplete)
    End Sub

#End Region

#Region " Breakpoint Helper Functions "

    Private Sub CheckAndSuspend()
        ' Loop until we can execute.  The loop is required rather than a simple if
        ' because there is a time between the return from WaitOne and the reset that we
        ' might receive another Suspend call.  Suspend() will see that we are suspended
        ' and return.  So we need to rewait.
        While Not canExecute.WaitOne(0, False)
            ChangeEvent(suspended, True)
            canExecute.WaitOne()
            ChangeEvent(suspended, False)
        End While
    End Sub

    Private Sub CheckAndFireBreakpoint(ByVal events As IDTSComponentEvents, ByVal breakpointID As Integer)
        ' If the breakpoint is enabled, fire it.
        If Me.debugModeValue <> 0 AndAlso Me.bpm.IsBreakpointTargetEnabled(breakpointID) Then
            ' Enter a suspend mode before firing the breakpoint.  Firing the breakpoint
            ' will cause the runtime to call Suspend on this task.  Since we are blocked
            ' on the breakpoint, we are suspended, so indicate that.
            ChangeEvent(suspended, True)
            events.FireBreakpointHit(Me.bpm.GetBreakpointTarget(breakpointID))
            ChangeEvent(suspended, False)
        End If

        ' Check for a suspension for two reasons: 1. If we are at a point where we could
        ' fire a breakpoint, we are at a valid suspend point.  Even if we didn't hit a
        ' breakpoint, the runtime may have called suspend, so check for it.  2. Between
        ' the return from OnBreakpointHit and the reset of the event, it is possible to have
        ' received a suspend call that we returned from because we were suspended.  We need
        ' to be sure it is ok to continue executing now.
        CheckAndSuspend()
    End Sub

    Shared Sub ChangeEvent(ByVal e As ManualResetEvent, ByVal shouldSet As Boolean)

        If e Is Nothing Then
            Throw New ArgumentNullException("e")
        End If

        Dim succeeded As Boolean

        If shouldSet Then
            succeeded = e.Set()
        Else
            succeeded = e.Reset()
        End If

        If Not succeeded Then
            Throw New Exception("Synchronization object failed.")
        End If
    End Sub

#End Region

End Class
